// 导入数据库操作模块
const db = require('../../db/index')
const { getCurrentTime } = require('../../utils/user')

// 获取诗单信息
exports.getPoemsList = (req, res) => {
    const { pageNum0, pageNum1, pageSize0, pageSize1 } = req.body
    let starNum0 = (pageNum0 - 1) * pageSize0
    let starNum1 = (pageNum1 - 1) * pageSize1
    // const sql = `select u.uid, u.nickname, u.user_id, pl.pl_name, pl.pl_cover, pl.pl_synopsis, pl.pl_time, pl.pl_type from poems_list pl, user_detail u, poems_list_user plu where plu.uid=u.uid and plu.pl_id=pl.pl_id and plu.is_create=1 and pl.make_private=0 and u.u_state=1 and pl.pl_state=1`
    const sql1 = `select count(distinct(pl_id)) as total from poems_list pl, user_detail u where pl.make_private=0 and pl.pl_state=1 and pl.uid=u.uid and u.ua_id!=10001 and u.u_state=1`
    const sql2 = `select count(distinct(pl_id)) as total from poems_list pl, user_detail u where pl.make_private=0 and pl.pl_state=1 and pl.uid=u.uid and u.ua_id=10001 and u.u_state=1`
    const adminSql = `select u.uid, pl.pl_id, pl.pl_name, pl.pl_cover, pl.pl_synopsis, pl.pl_time from poems_list pl, user_detail u where pl.make_private=0 and pl.pl_state=1 and pl.uid=u.uid and u.ua_id!=10001 and u.u_state=1 limit ${starNum0}, ${pageSize0}` 
    const userSql = `select u.uid, pl.pl_id, pl.pl_name, pl.pl_cover, pl.pl_synopsis, pl.pl_time from poems_list pl, user_detail u where pl.make_private=0 and pl.pl_state=1 and pl.uid=u.uid and u.ua_id=10001 and u.u_state=1 limit ${starNum1}, ${pageSize1}`
    db.query(`${sql1};${sql2};${adminSql};${userSql}`, (err, result) => {
        if(err) return res.operationSuccess('failure', 'ERR090', '获取诗单失败！', err)
        let arr = []
        return res.operationSuccess('success', '', '获取诗单成功！', {'admin': { total: result[0][0].total, results: result[2]}, 'user': {total: result[1][0].total, results: result[3]}})
    })
}

// 获取首页推荐的诗单
exports.getRecommentPoemsList = (req, res) => {
    const { pageNum, pageSize } = req.body
    let starNum = (pageNum - 1) * pageSize
    const sql = `select u.uid, u.nickname, pl.pl_id, pl.pl_name, pl.pl_cover, pl.pl_synopsis, pl.pl_time from poems_list pl, user_detail u where pl.make_private=0 and pl.pl_state=1 and pl.uid=u.uid and u.u_state=1 order by u.ua_id desc limit ${starNum}, ${pageSize}`
    db.query(sql, (err, result) => {
        if(err) return res.operationSuccess('failure', 'ERR090', '获取诗单失败！', err)
        return res.operationSuccess('success', '', '获取诗单成功！', result)
    })
}

// 用户首页获取全部诗单信息
exports.getPoemsListByUser = (req, res) => {
    const { pageNum0, pageSize0 } = req.body
    let starNum0 = (pageNum0 - 1) * pageSize0
    const sql1 = `select count(distinct(pl_id)) as total from poems_list pl, user_detail u where pl.make_private=0 and pl.pl_state=1 and pl.uid=u.uid and u.u_state=1`
    const sql2 = `select u.uid, pl.pl_id, pl.pl_name, pl.pl_cover, pl.pl_synopsis, pl.pl_time from poems_list pl, user_detail u where pl.make_private=0 and pl.pl_state=1 and pl.uid=u.uid and u.u_state=1 limit ${starNum0}, ${pageSize0}`
    db.query(`${sql1};${sql2}`, (err, result) => {
        if(err) return res.operationSuccess('failure', 'ERR090', '获取诗单失败！', err)
        return res.operationSuccess('success', '', '获取诗单成功！', { total: result[0][0].total, results: result[1]})
    })
}

// 根据用户id获取诗单信息
// 获取用户创建的所有诗单信息
function getUserPoetryLists(uid) {
    return new Promise((resolve, reject) => {
        const sql = `select * from poems_list where uid='${uid}' and pl_state=1`
        db.query(sql, (err, result) => {
            if(err) reject(err)
            else resolve(result)
        })
    })
}
// 获取某个诗单的诗词数量
function getPoemsCountInList(pl_id) {
    return new Promise((resolve, reject) => {
        const sql = `select COUNT(*) AS count from poems_list_poetry where pl_id='${pl_id}' and plp_state=1`
        db.query(sql, (err, results) => {
            if(err) reject(err)
            else resolve(results[0].count)
        })
    })
}
// 获取用户创建的所有诗单信息，并计算每个诗单里的诗词数量
async function getUserPoetryListsWithPoemsCount(uid) {
    const poetryLists = await getUserPoetryLists(uid);
    // 查询每个诗单里的诗词数量
    const promises = poetryLists.map((list) =>
        getPoemsCountInList(list.pl_id).then((count) => ({
            ...list,
            poetry_count: count,
        }))
    )
    return Promise.all(promises);
}
exports.getPoemsListByUid = async (req, res) => {
    const { uid } = req.auth
    try {
        const poetryLists = await getUserPoetryListsWithPoemsCount(uid);
        return res.operationSuccess('success', '', '获取诗单成功！', poetryLists)
    } catch(error) {
        return res.operationSuccess('failure', 'ERR090', '获取诗单失败！', error)
    }
}

// 上传诗单图片
exports.uploadPoemsListCover = (req, res) => {
    const { pl_id } = req.body
    let file = req.file
    let pl_cover = ''
    file ? pl_cover = `/api/out/serverImage/cover/poems-list/${file.filename}` : pl_cover = '无'
    const sql = `update poems_list set pl_cover='${pl_cover}' where pl_id=${pl_id}`
    db.query(sql, (err, result) => {
        console.log('err', err)
        if(err) return res.operationSuccess('failure', 'ERR096', '上传诗单图片失败！', err)
        return res.operationSuccess('success', '', '上传诗单图片成功！', result)
    })
}

// 创建诗单
exports.createPoemsList = (req, res) => {
    console.log('auth', req.auth)
    console.log('req', req)
    let uid = req.auth.uid
    let pl_type = '' 
    // req.auth.ua_id == 10001 ? pl_type = 'U' : pl_type = 'A'
    let { pl_name, pl_synopsis, make_private } = req.body
    let file = req.file
    let pl_cover = ''
    file ? pl_cover = `/api/out/serverImage/cover/poems-list/${file.filename}` : pl_cover = '无'
    const pl_time = getCurrentTime('date')
    const sql = `insert into poems_list(uid, pl_name, pl_cover, pl_synopsis, make_private, pl_time, pl_state) 
                 values('${uid}', '${pl_name}', '${pl_cover}', '${pl_synopsis}', ${make_private}, '${pl_time}', 1)`
    db.query(sql, (err, result) => {
        if(err) return res.operationSuccess('failure', 'ERR091', '创建诗单失败！', err)
        return res.operationSuccess('success', '', '创建诗单成功！', [])
    })
}

// 用户创建诗单
exports.userCreatePoemsList = (req, res) => {
    console.log('auth', req.auth)
    console.log('req', req)
    let uid = req.auth.uid
    let { pl_name, make_private } = req.body
    const pl_time = getCurrentTime('date')
    const sql = `insert into poems_list(uid, pl_name, pl_cover, pl_synopsis, make_private, pl_time, pl_state) 
                 values('${uid}', '${pl_name}', '无', '', ${make_private}, '${pl_time}', 1)`
    db.query(sql, (err, result) => {
        if(err) return res.operationSuccess('failure', 'ERR091', '创建诗单失败！', err)
        return res.operationSuccess('success', '', '创建诗单成功！', [])
    })
}

// 用户更新诗单
exports.userUpdatePoemsList = (req, res) => {
    console.log('auth', req.auth)
    console.log('req', req)
    let uid = req.auth.uid
    let { pl_name, make_private } = req.body
    const pl_time = getCurrentTime('date')
    const sql = `insert into poems_list(uid, pl_name, pl_cover, pl_synopsis, make_private, pl_time, pl_state) 
                 values('${uid}', '${pl_name}', '无', '', ${make_private}, '${pl_time}', 1)`
    db.query(sql, (err, result) => {
        if(err) return res.operationSuccess('failure', 'ERR091', '创建诗单失败！', err)
        return res.operationSuccess('success', '', '创建诗单成功！', [])
    })
}

// 将诗歌加入诗单
exports.addPoetryToPoemsList = (req, res) => {
    const { pl_id, pid }  = req.body
    const selectSql = `select * from poems_list_poetry where pl_id='${pl_id}' and pid='${pid}'`
    db.query(selectSql, (err1, result1) => {
        if(err1) return res.operationSuccess('failure', 'ERR092', '将诗歌加入诗单失败！', err1)
        if(result1.length != 0) return res.operationSuccess('failure', 'ERR092', '该诗歌已存在！', [])
        const sql = `insert into poems_list_poetry(pl_id, pid, plp_state) values(${pl_id}, ${pid}, 1)`
        db.query(sql, (err2, result2) => {
            if(err2) return res.operationSuccess('failure', 'ERR092', '将诗歌加入诗单失败！', err2)
            return res.operationSuccess('success', '', '将诗歌加入诗单成功！', [])
        })
    })
}

// 根据用户id和名称搜索诗单
exports.searchPoemsListByNameAndUid = (req, res) => {
    const { pl_name }  = req.body
    const { uid } = req.auth
    const sql = `select * from poems_list where pl_name like '%${pl_name}%' and uid='${uid}'`
    db.query(sql, (err, result) => {
        if(err) return res.operationSuccess('failure', 'ERR093', '根据名称搜索诗单失败！', err)
        return res.operationSuccess('success', '', '根据名称搜索诗单成功！', result)
    })
}

// 根据诗单名称/用户账号搜索诗单
exports.searchPoemsListByNameOrUid = (req, res) => {
    const { pageNum0, pageNum1, pageSize0, pageSize1, searchText, searchType } = req.body
    let starNum0 = (pageNum0 - 1) * pageSize0
    let starNum1 = (pageNum1 - 1) * pageSize1
    let type = searchType == 'N' ? 'pl_name' : 'uid'
    // const sql = `select u.uid, u.nickname, u.user_id, pl.pl_name, pl.pl_cover, pl.pl_synopsis, pl.pl_time, pl.pl_type from poems_list pl, user_detail u, poems_list_user plu where plu.uid=u.uid and plu.pl_id=pl.pl_id and plu.is_create=1 and pl.make_private=0 and u.u_state=1 and pl.pl_state=1`
    const sql1 = `select count(distinct(pl_id)) as total from poems_list pl, user_detail u where pl.make_private=0 and pl.pl_state=1 and pl.uid=u.uid and u.ua_id!=10001 and pl.${type} like '%${searchText}%'`
    const sql2 = `select count(distinct(pl_id)) as total from poems_list pl, user_detail u where pl.make_private=0 and pl.pl_state=1 and pl.uid=u.uid and u.ua_id=10001 and pl.${type} like '%${searchText}%'`
    const adminSql = `select u.uid, pl.pl_name, pl.pl_cover, pl.pl_synopsis, pl.pl_time from poems_list pl, user_detail u where pl.make_private=0 and pl.pl_state=1 and pl.uid=u.uid and u.ua_id!=10001 and pl.${type} like '%${searchText}%' limit ${starNum0}, ${pageSize0}` 
    const userSql = `select u.uid, pl.pl_name, pl.pl_cover, pl.pl_synopsis, pl.pl_time from poems_list pl, user_detail u where pl.make_private=0 and pl.pl_state=1 and pl.uid=u.uid and u.ua_id=10001 and pl.${type} like '%${searchText}%' limit ${starNum1}, ${pageSize1}`
    db.query(`${sql1};${sql2};${adminSql};${userSql}`, (err, result) => {
        if(err) return res.operationSuccess('failure', 'ERR090', '获取诗单失败！', err)
        let arr = []
        return res.operationSuccess('success', '', '获取诗单成功！', {'admin': { total: result[0][0].total, results: result[2]}, 'user': {total: result[1][0].total, results: result[3]}})
    })
}

// 根据id删除诗单信息
exports.deletePoemsListById = (req, res) => {
    const { pl_id } = req.body
    const sql = `update poems_list set pl_state=0 where pl_id in (${ pl_id })`
    db.query(sql, (err, results) => {
        if(err) return res.operationSuccess('failure', 'ERR092', '删除诗单信息失败！', err)
        return res.operationSuccess('success', '', '删除诗单信息成功！', [])
    })
}

// 根据id获取诗单详情
exports.searchPoemsListDetailByPlId = (req, res) => {
    const { pl_id } = req.body
    const sql1 = `select u.uid, u.nickname, u.avator, pl.pl_name, pl.pl_cover, pl.pl_time, pl.pl_synopsis, pl.make_private from poems_list pl, user_detail u where pl.pl_state=1 and pl.pl_id=${pl_id} and pl.uid=u.uid and u.u_state=1`
    const sql2 = `select a.a_name as author, p.pid, p.p_dynasty as dynasty, p.p_title as title, p.p_content as content from poems_list pl, poems_list_poetry plp, poetry2 p, author a where pl.pl_state=1 and pl.pl_id=${pl_id} and plp.pl_id=${pl_id} and plp.pid=p.pid and a.author_id=p.aid`
    // const sql1 = `select u.uid, u.nickname, u.avator, pl.pl_name, pl.pl_cover, pl.pl_time, pl.pl_synopsis from poems_list pl, user_detail u where pl.pl_state=1 and pl.pl_id=${pl_id} and pl.uid=u.uid and u.u_state=1 and pl.make_private=0`
    // const sql2 = `select a.a_name as author, p.p_dynasty as dynasty, p.p_title as title, p.p_content as content from poems_list pl, poems_list_poetry plp, poetry2 p, author a where pl.pl_state=1 and pl.pl_id=${pl_id} and plp.pl_id=${pl_id} and plp.pid=p.pid and pl.make_private=0 and a.author_id=p.aid`
    db.query(`${sql1};${sql2}`, (err, results) => {
        if(err) return res.operationSuccess('failure', 'ERR095', '根据诗单id获取详细信息失败！', err)
        let obj = {
            poemsList: results[0],
            poetryList: results[1]
        }
        return res.operationSuccess('success', '', '根据诗单id获取详细信息成功！', obj)
    })
}

// 收藏诗单
exports.collectPoems = (req, res) => {
    const { pl_id, pl_collect } = req.body
    let msg = ''
    pl_collect != 'true' ? msg = '收藏成功' : msg = '取消收藏'
    let collect = pl_collect != 'true' ? 0 : 1
    const { uid } = req.auth
    const sql = `select * from poem_list_collect where pl_id=${pl_id} and uid='${uid}'`
    db.query(sql, (err, results) => {
        if(err) return res.operationSuccess('failure', 'ERR097', '收藏诗单失败！', err)
        if(results.length != 0) {
            const updateSql = `update poem_list_collect set is_collect=${collect} where pl_id=${pl_id} and uid='${uid}'`
            db.query(updateSql, (updateErr, updateRes) => {
                if(updateErr) return res.operationSuccess('failure', 'ERR097', '收藏诗单失败！', updateErr)
                return res.operationSuccess('success', '', '收藏诗单成功！', msg)
            })
        } else {
            const updateSql = `insert into poem_list_collect(is_collect, pl_id, uid, plc_state) values(${collect}, ${pl_id}, '${uid}', 1)`
            db.query(updateSql, (updateErr, updateRes) => {
                if(updateErr) return res.operationSuccess('failure', 'ERR097', '收藏诗单失败！', updateErr)
                return res.operationSuccess('success', '', '收藏诗单成功！', msg)
            })
        }
    })
}

// 查询收藏信息
exports.queryCollectPoemsInfo = (req, res) => {
    let { pl_id } = req.body
    const { uid } = req.auth
    const selectSql = `select * from poem_list_collect where uid='${uid}' and pl_id=${pl_id} and plc_state=1`
    db.query(selectSql, (err1, res1) => {
        if(err1) return res.operationSuccess('failure', 'ERR058', '查询收藏信息失败！', err1)
        return res.operationSuccess('success', '', '查询收藏信息成功', res1)
    })
}

// 编辑诗单
exports.editPoemsListInfo = (req, res) => {
    const file = req.file
    console.log(req.query)
    console.log(req.body)
    let poemsName = ''
    let synopsis = ''
    let poemsId = ''
    let isPrivate = ''
    // let { poemsName, synopsis, isPrivate, poemsId } = req.body
    if(JSON.stringify(req.query) != '{}') {
        poemsName = req.query.poemsName
        synopsis = req.query.synopsis
        isPrivate = req.query.isPrivate
        poemsId = req.query.poemsId
    } else {
        poemsName = req.body.poemsName
        synopsis = req.body.synopsis
        isPrivate = req.body.isPrivate
        poemsId = req.body.poemsId
    }
    // let pl_cover = ''
    // file ? pl_cover = `/api/out/serverImage/cover/${file.filename}` : pl_cover = ''
    // const pl_time = getCurrentTime('date')
    let make_private = isPrivate == 'true' ? 1 : 0
    let sql = ''
    if(file) {
        sql = `update poems_list set pl_name='${poemsName}', pl_synopsis='${synopsis}', make_private=${make_private}, pl_cover='/api/out/serverImage/cover/poems-list/${file.filename}' where pl_id=${poemsId}`
    } else {
        sql = `update poems_list set pl_name='${poemsName}', pl_synopsis='${synopsis}', make_private=${make_private} where pl_id=${poemsId}`
    }
    db.query(sql, (err, result) => {
        if(err) return res.operationSuccess('failure', 'ERR098', '编辑诗单失败！', err)
        return res.operationSuccess('success', '', '编辑诗单成功！', [])
    })
}
